﻿Imports System.Data
Imports System.Data.Common

    ''' <summary>
    ''' 一般的表创建类
    ''' </summary>
    ''' <remarks></remarks>
    Public Class CreateTable
        Implements ITable

        Public Function CreateTable(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITable.CreateTable

            Dim sqlCreate As String = ""
            Dim sql As String = ""

            Dim dt表 As New DataTable
            Dim dt列 As New DataTable
            Dim dt表列 As New DataTable

            '获取所有表
            sql = "select name,object_id from sys.tables"
            dt表 = CommonConUtil.GetTable(sql)

            '获取所有列
            sql = " select c.object_id,c.name columnname,c.max_length,c.precision,c.scale,t.name typename from sys.columns c "
            sql += " LEFT JOIN sys.types t"
            sql += " on c.system_type_id=t.system_type_id"
            sql += " where t.name<>'sysname'"
            dt列 = CommonConUtil.GetTable(sql)

            Dim dv As DataView = dt列.DefaultView

            '创建表
            For rowTable As Integer = 0 To dt表.Rows.Count - 1

                dv.RowFilter = "object_id=" & dt表.Rows(rowTable)("object_id")
                dt表列 = dv.ToTable
                sqlCreate = CommonCreateListenDb.DbTableToListenTable(dt表列, dt表.Rows(rowTable)("name"), 前缀)

                '创建
                CommonConUtil.ExecuteSql(sqlCreate)
                RaiseEvent CreateTableEvent("创建表 " & 前缀 + "_" & CStr(dt表.Rows(rowTable)("name")))
            Next

            CommonConUtil.ExecuteSql(CommonCreateListenDb.Create_Table_T_Wys_Key(前缀))
            RaiseEvent CreateTableEvent("创建表 " + 前缀 + "_Key")

            CommonConUtil.ExecuteSql(CommonCreateListenDb.Create_Table_T_Wys_Table(前缀))
            RaiseEvent CreateTableEvent("创建表 " + 前缀 + "_Table")

            CommonConUtil.ExecuteSql(CommonCreateListenDb.Create_Table_XSC_Control(前缀))
            RaiseEvent CreateTableEvent("创建表 " + 前缀 + "_Control")

            CommonConUtil.ExecuteSql(CommonCreateListenDb.Create_Table_XSC_Count(前缀))
            RaiseEvent CreateTableEvent("创建表 " + 前缀 + "_Count")

            sqlCreate = "Insert into " + 前缀 + "_Key(Key_Timer,Descripion) values('" + Now.ToString("yyyyMMddHHmmss.fff") + "',N'王永胜数据库监控')"
            CommonConUtil.ExecuteSql(sqlCreate)

            sqlCreate = "Insert into " + 前缀 + "_Control(Kubun,Prefix,ID,Status) values('001','" + 前缀 + "',1,1)"
            CommonConUtil.ExecuteSql(sqlCreate)

            CommonConUtil.ExecuteSql(CommonCreateListenDb.Create_P_T_Wys_Table(前缀))
            RaiseEvent CreateTableEvent("创建存储过程 " + 前缀 + "_P_Table")

        End Function

        Public Function DeleteTable(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITable.DeleteTable

            Dim sqlCreate As String = ""
            Dim sql As String = ""

            Dim dt表 As New DataTable
            Dim dt列 As New DataTable
            Dim dt表列 As New DataTable
            Dim dt控制表 As New DataTable

            sql = "Select * from " + 前缀 + "_Control"
            dt控制表 = CommonConUtil.GetTable(sql)

            '获取所有表
            sql = "select name,object_id from sys.tables"
            dt表 = CommonConUtil.GetTable(sql)

            Dim str删除前缀 As String = 前缀
            If dt控制表.Rows.Count > 0 Then
                If CStr(dt控制表.Rows(0)("prefix")).ToLower.Trim <> "" Then
                    str删除前缀 = CStr(dt控制表.Rows(0)("prefix")).Trim
                End If
            End If

            '删除表
            For rowTable As Integer = 0 To dt表.Rows.Count - 1
                '存在先删除
                If CStr(dt表.Rows(rowTable)("name")).ToLower.StartsWith(str删除前缀.ToLower) OrElse _
                CStr(dt表.Rows(rowTable)("name")).ToLower.StartsWith("wys") OrElse _
                CStr(dt表.Rows(rowTable)("name")).ToLower.StartsWith("t_" & str删除前缀.ToLower) Then
                    CommonConUtil.ExecuteSql(" drop table " & CStr(dt表.Rows(rowTable)("name")))
                    RaiseEvent DeleteTableEvent("删除表 " & CStr(dt表.Rows(rowTable)("name")))
                End If
            Next

            CommonConUtil.ExecuteSql(" drop PROCEDURE " + str删除前缀 + "_P_Table")
            RaiseEvent DeleteTableEvent("删除存储过程 " + str删除前缀 + "_P_Table")

        End Function

        Public Function UpdateTable(ByVal dbConnection As System.Data.Common.DbConnection, ByVal 前缀 As String) As Boolean Implements IListenDb.ITable.UpdateTable

        End Function

        Public Event CreateTableEvent(ByVal table As String) Implements IListenDb.ITable.CreateTableEvent
        Public Event DeleteTableEvent(ByVal table As String) Implements IListenDb.ITable.DeleteTableEvent
        Public Event UpdateTableEvent(ByVal table As String) Implements IListenDb.ITable.UpdateTableEvent

    End Class
